package com.example.fmgidsprototype.data;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;
import android.provider.BaseColumns;
import android.util.Log;

import com.example.fmgidsprototype.data.CategoryTable.CategoryColumns;
import com.example.fmgidsprototype.data.VenueCategoryTable.VenueCategoryColumns;
import com.example.fmgidsprototype.data.VenueLocationTable.VenueLocationColumns;
import com.example.fmgidsprototype.data.VenueTable.VenueColumns;
import com.example.fmgidsprototype.model.Category;
import com.example.fmgidsprototype.model.Review;
import com.example.fmgidsprototype.model.Venue;

public class VenueDao implements Dao<Venue>{
	
	private SQLiteDatabase db;
	private SQLiteStatement insertStatement;
	public static final String INSERT = "insert into " + VenueTable.TABLE_NAME + "(" +
				VenueColumns.JSON_ID + ", " + VenueColumns.NAME + ", " + 
				VenueColumns.DESCRIPTION + ", " + VenueColumns.ADDRESS +
				", " + VenueColumns.THUMB_NAME + ", " + VenueColumns.IMAGE_URL + ", " +
				VenueColumns.URL + ", " + VenueColumns.EMAIL + ", " + VenueColumns.PHONE + 
				", " + VenueColumns.AUDIO_PATH + ", " + VenueColumns.RATING + 
				") values (?,?,?,?,?,?,?,?,?,?,?)";
	
	public VenueDao(SQLiteDatabase db){
		this.db = db;
		this.insertStatement = db.compileStatement(VenueDao.INSERT);
	}

	@Override
	public long save(Venue venue) {
		insertStatement.clearBindings();
		insertStatement.bindLong(1, venue.getJsonId());
		insertStatement.bindString(2, venue.getName());
		insertStatement.bindString(3, venue.getDescription());
		insertStatement.bindString(4, venue.getAddress());
		insertStatement.bindString(5, venue.getThumbName());
		insertStatement.bindString(6, venue.getPicUrl());
		insertStatement.bindString(7, venue.getUrl());
		insertStatement.bindString(8, venue.getEmail());
		insertStatement.bindString(9, venue.getPhone());
		insertStatement.bindString(10, venue.getAudioPath());
		insertStatement.bindDouble(11, venue.getRating());
		try{
			return insertStatement.executeInsert();
		}
		catch(Exception e){
			return -1;
		}
	}

	@Override
	public long update(Venue venue) {
		final ContentValues values = new ContentValues();
		values.put(VenueColumns.JSON_ID, venue.getJsonId());
		values.put(VenueColumns.NAME, venue.getName());
		values.put(VenueColumns.DESCRIPTION, venue.getDescription());
		values.put(VenueColumns.ADDRESS, venue.getAddress());
		values.put(VenueColumns.THUMB_NAME, venue.getThumbName());
		values.put(VenueColumns.IMAGE_URL, venue.getPicUrl());
		values.put(VenueColumns.URL, venue.getUrl());
		values.put(VenueColumns.EMAIL, venue.getEmail());
		values.put(VenueColumns.PHONE, venue.getPhone());
		values.put(VenueColumns.AUDIO_PATH, venue.getAudioPath());
		values.put(VenueColumns.RATING, venue.getRating());
		try{
			return db.update(VenueTable.TABLE_NAME, values, VenueColumns.JSON_ID + " = ?", new String[]{
					String.valueOf(venue.getJsonId())
				});
		}
		catch(Exception e){
			return -1;
		}
	}

	@Override
	public void delete(Venue venue) {
		if(venue.getId() > 0){
			db.delete(VenueTable.TABLE_NAME, BaseColumns._ID + " = ?", new String[]{
					String.valueOf(venue.getId())
			});
		}
	}

	@Override
	public Venue get(long id) {
		Venue venue = null;
		Cursor c = db.query(VenueTable.TABLE_NAME, new String[]{
				BaseColumns._ID, VenueColumns.JSON_ID, VenueColumns.NAME, 
				VenueColumns.DESCRIPTION, VenueColumns.ADDRESS,
				VenueColumns.THUMB_NAME, VenueColumns.IMAGE_URL, 
				VenueColumns.URL, VenueColumns.EMAIL, VenueColumns.PHONE,
				VenueColumns.AUDIO_PATH, VenueColumns.RATING
		}, BaseColumns._ID + " = ?", new String[]{String.valueOf(id)}, null, null, null, "1");
		if(c.moveToFirst()){
			venue = createVenueFromCursor(c);
			Log.d("VENUEQUERY", "venue found: " + venue.getName());
		}
		if(!c.isClosed())
			c.close();
		return venue;
	}
	
	private Venue getWithLocation(long id){
		Venue venue = null;
		String query = "select V." + BaseColumns._ID + ", " + VenueColumns.JSON_ID + ", " +
				VenueColumns.NAME + ", " + VenueColumns.DESCRIPTION + ", " + VenueColumns.ADDRESS +
				", " + VenueColumns.THUMB_NAME + ", " + VenueColumns.IMAGE_URL + ", " +
				VenueColumns.URL + ", " + VenueColumns.EMAIL + ", " + VenueColumns.PHONE + ", " +
				VenueColumns.AUDIO_PATH + ", " + VenueColumns.RATING + ", " +
				VenueLocationColumns.LONGITUDE + ", " + VenueLocationColumns.LATITUDE + ", " 
				+ VenueLocationColumns.DISTANCE_FROM_ZERO +
				" from " + VenueTable.TABLE_NAME + " as V," + VenueLocationTable.TABLE_NAME + 
				" where V._id = " + VenueLocationColumns.VENUE + " and V._id = ?";
		Cursor c = db.rawQuery(query, new String[]{String.valueOf(id)});
		if(c.moveToFirst()){
			venue = createVenueFromCursor(c);
			List<Category> categories = this.categoriesByVenue(venue.getId());
			venue.setCategories(categories);
			Log.d("VENUEQUERY", "venue found: " + venue.getName());
		}
		if(!c.isClosed())
			c.close();
		return venue;
	}
	

	@Override
	public List<Venue> getAll() {
		List<Venue> list = new ArrayList<Venue>();
		Cursor c = db.query(VenueTable.TABLE_NAME, new String[]{
				BaseColumns._ID, VenueColumns.JSON_ID, VenueColumns.NAME, 
				VenueColumns.DESCRIPTION, VenueColumns.ADDRESS,
				VenueColumns.THUMB_NAME, VenueColumns.IMAGE_URL, 
				VenueColumns.URL, VenueColumns.EMAIL, VenueColumns.PHONE,
				VenueColumns.AUDIO_PATH, VenueColumns.RATING
		}, null, null, null, null, null);
		Log.d("QUERY_LOG", "return from query getallvenues " + c.getColumnCount());
		Log.d("QUERY_LOG", "return from query getallvenues " + c.getCount());
		if(c.moveToFirst()){
			do{
				Venue venue = createVenueFromCursor(c);
				if(venue != null)
					list.add(venue);
			}
			while(c.moveToNext());
		}
		if(!c.isClosed())
			c.close();
		return list;
	}
	
	public List<Venue> getAllVenuesWithLocations(){
		List<Venue> list = new ArrayList<Venue>();
		String query = "select V." + BaseColumns._ID +  ", " + VenueColumns.JSON_ID + ", " +
				VenueColumns.NAME + ", " + VenueColumns.DESCRIPTION + ", " + VenueColumns.ADDRESS +
				", " + VenueColumns.THUMB_NAME + ", " + VenueColumns.IMAGE_URL + ", " + VenueColumns.URL +
				", " + VenueColumns.EMAIL + ", " + VenueColumns.PHONE + ", " + VenueColumns.AUDIO_PATH +
				", " + VenueColumns.RATING + ", " + 
				VenueLocationColumns.LONGITUDE + ", " + VenueLocationColumns.LATITUDE + ", " +
				VenueLocationColumns.DISTANCE_FROM_ZERO +
				" from " + VenueTable.TABLE_NAME + " as V" + "," + VenueLocationTable.TABLE_NAME +
				" where " + VenueLocationColumns.VENUE + " = V._id";
		Cursor c = db.rawQuery(query, null);
		if(c.moveToFirst()){
			do{
				Venue venue = createVenueFromCursor(c);
				List<Category> categories = this.categoriesByVenue(venue.getId());
				venue.setCategories(categories);
				if(venue != null)
					list.add(venue);
			}
			while(c.moveToNext());
		}
		if(!c.isClosed())
			c.close();
		return list;
	}
	
	public List<Venue> getAllVenuesForCategory(long catId){
		List<Venue> list = new ArrayList<Venue>();
		String query = "select " + VenueCategoryColumns.VENUE_ID +
				" from " + VenueCategoryTable.TABLE_NAME + " where " +
				VenueCategoryColumns.CATEGORY_ID + " = ?";
		Cursor c = db.rawQuery(query, new String[]{String.valueOf(catId)});
		if(c.moveToFirst()){
			do{
				Log.d("QUERY", "returned id: " + c.getLong(0));
				Venue venue = this.getWithLocation(c.getLong(0));
				list.add(venue);
			}
			while(c.moveToNext());
		}
		return list;
	}
	
	private List<Category> categoriesByVenue(long venueId) {
		List<Category> list = new ArrayList<Category>();
		String query = "select C." + BaseColumns._ID + ", " + CategoryColumns.NAME +
				", " + CategoryColumns.DESCRIPTION + ", " + CategoryColumns.ICON_NAME +
				" from " + CategoryTable.TABLE_NAME + " as C," + VenueCategoryTable.TABLE_NAME +
				" where C._id = " + VenueCategoryColumns.CATEGORY_ID + " and " +
				VenueCategoryColumns.VENUE_ID + " = ?";
		Cursor c = db.rawQuery(query, new String[]{String.valueOf(venueId)});
		if(c.moveToFirst()){
			do{
				Category category = CategoryDao.createCategoryFromCursor(c);
				list.add(category);
			}
			while(c.moveToNext());
		}
		return list;
	}
	
	public long findVenue(long jsonId){
		long id = 0L;
		String query = "select _id from " + VenueTable.TABLE_NAME + " where " + VenueColumns.JSON_ID +
				" = ?";
		Cursor c = db.rawQuery(query, new String[]{String.valueOf(jsonId)});
		if(c.moveToFirst()){
			id = c.getLong(0);
		}
		return id;
	}
	
	
	public static Venue createVenueFromCursor(Cursor c){
		Venue venue = null;
		if(c != null){
			venue = new Venue();
			venue.setId(c.getLong(0));
			venue.setJsonId(c.getLong(1));
			venue.setName(c.getString(2));
			venue.setDescription(c.getString(3));
			venue.setAddress(c.getString(4));
			venue.setThumbName(c.getString(5));
			venue.setPicUrl(c.getString(6));
			venue.setUrl(c.getString(7));
			venue.setEmail(c.getString(8));
			venue.setPhone(c.getString(9));
			venue.setAudioPath(c.getString(10));
			venue.setRating((int)c.getLong(11));
			if(c.getColumnCount() == 15){
				venue.setLongitude(c.getDouble(12));
				venue.setLatitude(c.getDouble(13));
				venue.setDistanceFromZero(c.getLong(14));
			}
		}
		return venue;
	}

	public void deleteAll(){
		String deleteSql = "delete from " + VenueTable.TABLE_NAME ;
		db.execSQL(deleteSql);
	}

}
